﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Drawing;
using System.IO;
using System.Reflection;

namespace OALPTool
{
    /// <summary>
    /// DataGridView通用方法
    /// </summary>
    public class TEntranceClass
    {

        /// <summary>
        /// 构造函数 
        /// </summary>
        public TEntranceClass()
        {

        }

        /// <summary>
        /// DataGridView列格式设置  不重新刷新DataGridView
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <returns>设置成功，返回大于0的数；否则返回负数</returns>
        public int SAL_FrmColStyleSet(ref DataGridView dgv)
        {
            try
            {
                FrmColStyleSet frm = new FrmColStyleSet();
                if (frm.InitForm(dgv) < 0) return -1;

                frm.ShowDialog();

                if (frm.DialogResult == DialogResult.OK)
                {
                    dgv = frm.m_dgvC;
                    return 1;
                }
            }
            catch (Exception e)
            {
                TSysPublic.ShowErrMsg(e.ToString());
                return -1;
            }
            return 0;
        
        }

        /// <summary>
        /// DataGridView列格式设置  不重新刷新DataGridView
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="dgvname">列格式设置操作名称</param>
        /// <returns>设置成功，返回大于0的数；否则返回负数</returns>
        public int SAL_FrmColStyleSetEX(ref DataGridView dgv,string dgvname)
        {
            try
            {
                FrmColStyleSet frm = new FrmColStyleSet();
                if (frm.InitForm(dgv,dgvname) < 0) return -1;

                frm.ShowDialog();

                if (frm.DialogResult == DialogResult.OK)
                {
                    dgv = frm.m_dgvC;
                    return 1;
                }
            }
            catch (Exception e)
            {
                TSysPublic.ShowErrMsg(e.ToString());
                return -1;
            }
            return 0;

        }

        /// <summary>
        /// DataGridView列格式设置  重新刷新DataGridView
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="colList">DataGridView原有样式</param>
        /// <param name="dv">DataGridView数据源</param>
        /// <param name="iFrozen">冻结列数</param>
        /// <param name="dgvName">列格式设置操作名称</param>
        /// <returns>设置成功，返回大于0的数，否则返回负数</returns>
        public int SAL_FrmColStyleSetRefresh(ref DataGridView dgv, ArrayList colList, DataView dv, int iFrozen,string dgvName)
        {
            try
            {
                FrmColStyleSet frm = new FrmColStyleSet();
                if (frm.InitFormRefresh(dgv, colList, dv, dgvName) < 0) return -1;

                frm.ShowDialog();

                if (frm.DialogResult == DialogResult.OK)
                {
                    dgv = frm.m_dgvC;

                    return SAL_doFrozenColumn(ref dgv, iFrozen);

                }
            }
            catch (Exception e)
            {
                TSysPublic.ShowErrMsg(e.ToString());
                return -1;
            }
            return 0;
        }

        /// <summary>
        /// 冻结DataGirdView相应列数
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="iFrozen">需冻结的列数</param>
        /// <returns>操作成功，返回0；否则返回负数</returns>
        public int SAL_doFrozenColumn(ref DataGridView dgv, int iFrozen) 
        {
            int i = 0;
            for (int j = 0; j < dgv.Columns.Count; j++)
            {
                dgv.Columns[j].Frozen = false;
                dgv.Columns[j].DefaultCellStyle.BackColor = Color.White;
            }

            if (iFrozen <= 0) 
            {
                return 0;
            }

            for (int j = 0; j < dgv.Columns.Count; j++)
            {
                if (dgv.Columns[j].Visible == true)
                {
                    i++;
                }

                dgv.Columns[j].DefaultCellStyle.BackColor = Color.Gainsboro;
                dgv.Columns[j].Frozen = true;

                if (i == iFrozen)
                {
                    break;
                }
            }

            return 0;
        }

        /// <summary>
        /// 导出DataGridView中的数据到Excel文件 
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="TitleList">标题信息</param>
        public void SAL_DataGridViewToExcel(DataGridView dgv, ArrayList TitleList)
        {
            //申明保存对话框    
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀    
            dlg.DefaultExt = "xls ";
            //文件后缀列表    
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径    
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框    
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径    
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效    
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数    
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;

            //行数必须大于0    
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数必须大于0    
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //行数不可以大于65536    
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数不可以大于255    
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //验证以fileNameString命名的文件是否存在，如果存在删除它    
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            Microsoft.Office.Interop.Excel.Application objExcel = null;
            Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
            Microsoft.Office.Interop.Excel.Range r;
            try
            {
                //申明对象    
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见    
                objExcel.Visible = false;

                int displayColumnsCount = 1;
                int titlecount = 0;
                if (TitleList != null && TitleList.Count != 0)
                {
                    titlecount = TitleList.Count;
                }

                int headcount = 1 + titlecount;
                int colcount = dgv.ColumnCount;

                //向Excel中写入表格的标题   
                if (titlecount > 0)
                {
                    for (int i = 0; i < TitleList.Count; i++)
                    {
                        for (int j = 0; j < ((ArrayList)TitleList[i]).Count; j++)
                        {

                            r = objsheet.get_Range(objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])],
                                objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndCol"])]);
                            r.Merge(0);

                            objExcel.Cells[i + 1, Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])] = ((DataRow)((ArrayList)TitleList[i])[j])["Text"].ToString();

                            //对齐方式 0-为居中，1-为居左，2-为居右
                            string strAlign = ((DataRow)((ArrayList)TitleList[i])[j])["Align"].ToString();
                            if (strAlign.Equals("1"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                            }
                            else if (strAlign.Equals("2"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                            }
                            else
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                            }
                            if (i == 0)
                            {
                                r.Font.Size = 20;
                            }
                        }
                    }

                }

                //向Excel中写入表格的表头    
                displayColumnsCount = 1;
                for (int i = 1; i <= dgv.ColumnCount; i++)
                {
                    if (dgv.Columns[i - 1].Visible == true)
                    {

                        string strName = "";
                        string strFormat = "";
                        try
                        {
                            strName = dgv.Columns[i - 1].ValueType.Name;
                            strFormat = dgv.Columns[i - 1].DefaultCellStyle.Format;
                        }
                        catch (Exception)
                        {
                            strName = "";
                            strFormat = "";
                        }

                        if ((strName == "String") || (strName == "Char"))
                        {
                            objExcel.get_Range(objExcel.Cells[titlecount + 1, displayColumnsCount], objExcel.Cells[titlecount + rowscount + 1, displayColumnsCount]).NumberFormat = "@";
                        }
                        else
                        {
                            objExcel.get_Range(objExcel.Cells[titlecount + 1, displayColumnsCount], objExcel.Cells[titlecount + rowscount + 1, displayColumnsCount]).NumberFormat = strFormat;
                        }

                        objExcel.Cells[titlecount + 1, displayColumnsCount] = dgv.Columns[i - 1].HeaderText.Trim();
                        displayColumnsCount++;
                    }
                }
                r = objsheet.get_Range(objsheet.Cells[titlecount + 1, 1], objsheet.Cells[titlecount + 1, colcount]);
                r.Font.Bold = true;

                //向Excel中逐行逐列写入表格中的数据    
                for (int row = 0; row <= dgv.RowCount - 1; row++)
                {
                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (dgv.Columns[col].Visible == true)
                        {
                            try
                            {
                                objsheet.Cells[row + headcount + 1, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                                displayColumnsCount++;
                            }
                            catch (Exception)
                            {

                            }

                        }
                    }
                }
                //列宽自适应
                objsheet.Columns.EntireColumn.AutoFit();
                
                //画表格线
                r = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range(objsheet.Cells[1 + titlecount, 1], objsheet.Cells[headcount + dgv.RowCount, displayColumnsCount-1]);
                r.Borders.LineStyle = 1;
                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置左边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置上边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置右边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置下边线加粗
                r.WrapText = true;//自动换行

                //保存文件    
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用    
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();

                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        /// <summary>
        /// 导出DataGridView中的数据到Excel文件
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="TitleList">标题信息</param>
        /// <param name="htRowTreeList">行树信息</param>
        public void SAL_DataGridViewToExcel_Ex(DataGridView dgv, ArrayList TitleList, Hashtable htRowTreeList)
        {
            //申明保存对话框    
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀    
            dlg.DefaultExt = "xls ";
            //文件后缀列表    
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径    
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框    
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径    
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效    
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数    
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            //行数必须大于0    
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数必须大于0    
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //行数不可以大于65536    
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数不可以大于255    
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //验证以fileNameString命名的文件是否存在，如果存在删除它    
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            Microsoft.Office.Interop.Excel.Application objExcel = null;
            Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
            Microsoft.Office.Interop.Excel.Range r;
            try
            {
                //申明对象    
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见    
                objExcel.Visible = false;

                int displayColumnsCount = 1;
                int titlecount = 0;
                if (TitleList != null && TitleList.Count != 0)
                {
                    titlecount = TitleList.Count;
                }

                int headcount = 1 + titlecount;
                int colcount = dgv.ColumnCount;

                //向Excel中写入表格的标题   
                if (titlecount > 0)
                {
                    for (int i = 0; i < TitleList.Count; i++)
                    {
                        for (int j = 0; j < ((ArrayList)TitleList[i]).Count; j++)
                        {

                            r = objsheet.get_Range(objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])],
                                objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndCol"])]);
                            r.Merge(0);

                            objExcel.Cells[i + 1, Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])] = ((DataRow)((ArrayList)TitleList[i])[j])["Text"].ToString();

                            //对齐方式 0-为居中，1-为居左，2-为居右
                            string strAlign = ((DataRow)((ArrayList)TitleList[i])[j])["Align"].ToString();
                            if (strAlign.Equals("1"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                            }
                            else if (strAlign.Equals("2"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                            }
                            else
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                            }
                            if (i == 0)
                            {
                                r.Font.Size = 20;
                            }
                        }
                    }

                }

                //向Excel中写入表格的表头    
                for (int i = 1; i <= dgv.ColumnCount; i++)
                {
                    if (dgv.Columns[i - 1].Visible == true)
                    {

                        string strName = "";
                        string strFormat = "";
                        try
                        {
                            strName = dgv.Columns[i - 1].ValueType.Name;
                            strFormat = dgv.Columns[i - 1].DefaultCellStyle.Format;
                        }
                        catch (Exception)
                        {
                            strName = "";
                            strFormat = "";
                        }

                        if ((strName == "String") || (strName == "Char"))
                        {
                            objExcel.get_Range(objExcel.Cells[titlecount + 1, i], objExcel.Cells[titlecount + rowscount + 1, i]).NumberFormat = "@";
                        }
                        else
                        {
                            objExcel.get_Range(objExcel.Cells[titlecount + 1, i], objExcel.Cells[titlecount + rowscount + 1, i]).NumberFormat = strFormat;
                        }

                        objExcel.Cells[titlecount + 1, i] = dgv.Columns[i - 1].HeaderText.Trim();

                    }
                }
                r = objsheet.get_Range(objsheet.Cells[titlecount + 1, 1], objsheet.Cells[titlecount + 1, colcount]);
                r.Font.Bold = true;

                displayColumnsCount = 1;
                //向Excel中逐行逐列写入表格中的数据    
                for (int col = 0; col < colscount; col++) 
                {
                    if (dgv.Columns[col].Visible == true)
                    {
                        if (htRowTreeList.Contains(displayColumnsCount))
                        {
                            Hashtable htRowTree = (Hashtable)htRowTreeList[displayColumnsCount];

                            for (int row = 0; row < dgv.RowCount; row++) 
                            {
                                if (htRowTree.Contains(row))
                                {
                                    DataRow drCol = (DataRow)htRowTree[row];

                                    r = objsheet.get_Range(objsheet.Cells[Convert.ToInt32(drCol["BegRow"].ToString()) + headcount, Convert.ToInt32(drCol["BegCol"].ToString())],
                                        objsheet.Cells[Convert.ToInt32(drCol["EndRow"].ToString()) + headcount, Convert.ToInt32(drCol["EndCol"].ToString())]);
                                    r.Merge(0);

                                    try
                                    {
                                        objExcel.Cells[Convert.ToInt32(drCol["BegRow"].ToString()) + headcount, displayColumnsCount] = drCol["Text"].ToString();
                                    }
                                    catch (Exception ex) 
                                    {
                                        TSysPublic.ShowMsg(ex.ToString());
                                    }

                                    r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                                }
                            }
                        }
                        else
                        {
                            for (int row = 0; row < dgv.RowCount; row++)
                            {
                                try
                                {
                                    objsheet.Cells[row + headcount + 1, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                                }
                                catch (Exception)
                                {

                                }
                            }
                        }
                        displayColumnsCount++;
                    }
                }

                
                //列宽自适应
                objsheet.Columns.EntireColumn.AutoFit();

                //画表格线
                r = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range(objsheet.Cells[1 + titlecount, 1], objsheet.Cells[headcount + dgv.RowCount, colcount]);
                r.Borders.LineStyle = 1;
                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置左边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置上边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置右边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置下边线加粗
                r.WrapText = true;//自动换行

                //保存文件    
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用    
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();

                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }


        /// <summary>
        /// 导出DataGridViewHead中的数据到Excel文件
        /// </summary>
        /// <param name="dgv">DataGridViewHead</param>
        /// <param name="list">表头信息</param>
        /// <param name="TitleList">标题信息</param>
        public void SAL_DataGridViewHeadToExcel(DataGridViewHead dgv, ArrayList list, ArrayList TitleList)
        {
            //申明保存对话框    
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀    
            dlg.DefaultExt = "xls ";
            //文件后缀列表    
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径    
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框    
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径    
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效    
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数    
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            //行数必须大于0    
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数必须大于0    
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //行数不可以大于65536    
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数不可以大于255    
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //验证以fileNameString命名的文件是否存在，如果存在删除它    
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            Microsoft.Office.Interop.Excel.Application objExcel = null;
            Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
            Microsoft.Office.Interop.Excel.Range r;
            try
            {
                //申明对象    
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见    
                objExcel.Visible = false;

                int displayColumnsCount = 1;
                int titlecount = 0;
                if (TitleList != null && TitleList.Count != 0)
                {
                    titlecount = TitleList.Count;
                }

                int headcount = dgv.ColumnDeep + titlecount;
                int colcount = dgv.ColumnCount;

                //向Excel中写入表格的标题   
                if (titlecount > 0)
                {
                    for (int i = 0; i < TitleList.Count; i++)
                    {
                        for (int j = 0; j < ((ArrayList)TitleList[i]).Count; j++)
                        {

                            r = objsheet.get_Range(objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])],
                                objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndCol"])]);
                            r.Merge(0);

                            objExcel.Cells[i + 1, Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])] = ((DataRow)((ArrayList)TitleList[i])[j])["Text"].ToString();

                            //对齐方式 0-为居中，1-为居左，2-为居右
                            string strAlign = ((DataRow)((ArrayList)TitleList[i])[j])["Align"].ToString();
                            if (strAlign.Equals("1"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                            }
                            else if (strAlign.Equals("2"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                            }
                            else
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                            }
                            if (i == 0)
                            {
                                r.Font.Size = 20;
                            }
                        }
                    }

                }

                for (int rowtemp = 0; rowtemp <= dgv.RowCount - 1; rowtemp++)
                {

                    string strName = "";
                    string strFormat = "";
                    try
                    {
                        strName = dgv.Columns[rowtemp].ValueType.Name;
                        strFormat = dgv.Columns[rowtemp].DefaultCellStyle.Format;
                    }
                    catch (Exception)
                    {
                        strName = "";
                        strFormat = "";
                    }

                    if ((strName == "String") || (strName == "Char"))
                    {
                        objExcel.get_Range(objExcel.Cells[titlecount + 1, rowtemp + 1], objExcel.Cells[titlecount + dgv.ColumnDeep + rowscount + 1, rowtemp + 1]).NumberFormat = "@";
                    }
                    else
                    {
                        objExcel.get_Range(objExcel.Cells[titlecount + 1, rowtemp + 1], objExcel.Cells[titlecount + dgv.ColumnDeep + rowscount + 1, rowtemp + 1]).NumberFormat = strFormat;
                    }

                }


                //向Excel中写入表格的表头    
                if (headcount > 1 && list != null)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        for (int j = 0; j < ((ArrayList)list[i]).Count; j++)
                        {

                            r = objsheet.get_Range(objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)list[i])[j])["BegRow"]) + titlecount,
                                Convert.ToInt32(((DataRow)((ArrayList)list[i])[j])["BegCol"])],
                                objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)list[i])[j])["EndRow"]) + titlecount,
                                Convert.ToInt32(((DataRow)((ArrayList)list[i])[j])["EndCol"])]);

                            r.Merge(0);

                            objExcel.Cells[i + 1 + titlecount, Convert.ToInt32(((DataRow)((ArrayList)list[i])[j])["BegCol"])] = ((DataRow)((ArrayList)list[i])[j])["Text"].ToString();

                            r.Font.Bold = true;
                            r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        }
                    }
                }
                else
                {
                    for (int i = 1; i <= dgv.ColumnCount; i++)
                    {
                        if (dgv.Columns[i - 1].Visible == true)
                        {
                            objExcel.Cells[titlecount + 1, i] = dgv.Columns[i - 1].HeaderText.Trim();
                        }
                    }
                }

                //向Excel中逐行逐列写入表格中的数据    
                for (int row = 0; row <= dgv.RowCount - 1; row++)
                {
                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (dgv.Columns[col].Visible == true)
                        {
                            try
                            {
                                objsheet.Cells[row + headcount + 1, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                                displayColumnsCount++;
                            }
                            catch (Exception)
                            {

                            }

                        }
                    }
                }
                //列宽自适应
                objsheet.Columns.EntireColumn.AutoFit();

                //画表格线

                r = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range(objsheet.Cells[1 + titlecount, 1], objsheet.Cells[headcount + dgv.RowCount, colcount]);

                r.Borders.LineStyle = 1;
                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置左边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置上边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置右边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置下边线加粗

                r.WrapText = true;//自动换行

                //保存文件    
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用    
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();

                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }


        /// <summary>
        /// 导出DataView中的数据到Excel文件 
        /// </summary>
        /// <param name="dvExcel">DataView</param>
        /// <param name="HeaderList">表头信息</param>
        /// <param name="TitleList">标题信息</param>
        public void SAL_DataViewToExcel(DataView dvExcel,ArrayList HeaderList, ArrayList TitleList)
        {
            //申明保存对话框    
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀    
            dlg.DefaultExt = "xls ";
            //文件后缀列表    
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径    
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框    
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径    
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效    
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数    
            int rowscount = dvExcel.Count;
            int colscount = HeaderList.Count;
            //行数必须大于0    
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数必须大于0    
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //行数不可以大于65536    
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //列数不可以大于255    
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            //验证以fileNameString命名的文件是否存在，如果存在删除它    
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            Microsoft.Office.Interop.Excel.Application objExcel = null;
            Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
            Microsoft.Office.Interop.Excel.Range r;
            try
            {
                //申明对象    
                objExcel = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
                //设置EXCEL不可见    
                objExcel.Visible = false;

                int displayColumnsCount = 1;
                int titlecount = 0;
                if (TitleList != null && TitleList.Count != 0)
                {
                    titlecount = TitleList.Count;
                }

                int headcount = 1 + titlecount;
                int colcount = HeaderList.Count;

                //向Excel中写入表格的标题   
                if (titlecount > 0)
                {
                    for (int i = 0; i < TitleList.Count; i++)
                    {
                        for (int j = 0; j < ((ArrayList)TitleList[i]).Count; j++)
                        {

                            r = objsheet.get_Range(objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])],
                                objsheet.Cells[Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndRow"]),
                                Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["EndCol"])]);
                            r.Merge(0);

                            objExcel.Cells[i + 1, Convert.ToInt32(((DataRow)((ArrayList)TitleList[i])[j])["BegCol"])] = ((DataRow)((ArrayList)TitleList[i])[j])["Text"].ToString();

                            //对齐方式 0-为居中，1-为居左，2-为居右
                            string strAlign = ((DataRow)((ArrayList)TitleList[i])[j])["Align"].ToString();
                            if (strAlign.Equals("1"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                            }
                            else if (strAlign.Equals("2"))
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                            }
                            else
                            {
                                r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                            }
                            if (i == 0)
                            {
                                r.Font.Size = 20;
                            }
                        }
                    }

                }

                //向Excel中写入表格的表头    
                for (int i = 1; i <= HeaderList.Count; i++)
                {
                    object[] objHead = (object[])HeaderList[i - 1];
                    if (dvExcel.Table.Columns.Contains(objHead[0].ToString()))
                    {

                        string strName = "";
                        string strFormat = "";
                        try
                        {
                            strName = objHead[2].ToString();
                            strFormat = objHead[3].ToString();
                        }
                        catch (Exception)
                        {
                            strName = "";
                            strFormat = "";
                        }

                        if ((strName == "String") || (strName == "Char"))
                        {
                            objExcel.get_Range(objExcel.Cells[titlecount + 1, i], objExcel.Cells[titlecount + rowscount + 1, i]).NumberFormat = "@";
                        }
                        else
                        {
                            objExcel.get_Range(objExcel.Cells[titlecount + 1, i], objExcel.Cells[titlecount + rowscount + 1, i]).NumberFormat = strFormat;
                        }

                        objExcel.Cells[titlecount + 1, i] = objHead[1].ToString();
                    }
                }
                r = objsheet.get_Range(objsheet.Cells[titlecount + 1, 1], objsheet.Cells[titlecount + 1, colcount]);
                r.Font.Bold = true;

                //向Excel中逐行逐列写入表格中的数据    
                for (int row = 0; row <= dvExcel.Count - 1; row++)
                {
                    displayColumnsCount = 1;
                    for (int col = 0; col < HeaderList.Count; col++)
                    {
                        object[] objHead = (object[])HeaderList[col];
                        if (dvExcel.Table.Columns.Contains(objHead[0].ToString()))
                        {
                            try
                            {
                                objsheet.Cells[row + headcount + 1, displayColumnsCount] = dvExcel[row][objHead[0].ToString()].ToString().Trim();
                                displayColumnsCount++;
                            }
                            catch (Exception)
                            {

                            }

                        }
                    }
                }
                //列宽自适应
                objsheet.Columns.EntireColumn.AutoFit();

                //画表格线
                r = (Microsoft.Office.Interop.Excel.Range)objsheet.get_Range(objsheet.Cells[1 + titlecount, 1], objsheet.Cells[headcount + dvExcel.Count, colcount]);
                r.NumberFormatLocal = "@";

                r.Borders.LineStyle = 1;
                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置左边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置上边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置右边线加粗

                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//设置下边线加粗

                r.WrapText = true;//自动换行

                //保存文件    
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用    
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();

                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
            MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
    }
}
